package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.bo.Employee;
import edu.byu.intex2.data.bo.Person;
import java.sql.*;
import java.util.List;

/**
 * Employee Data Access Object
 * @author Jason Whiting
 */
public class EmployeeDAO
{

    /////////////////////////////////////////////
    ///   Singleton code
    private static EmployeeDAO instance = null;

    /** Creates a new instance of EmployeeDAO */
    private EmployeeDAO()
    {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized EmployeeDAO getInstance()
    {
        if (instance == null)
        {
            instance = new EmployeeDAO();
        }
        return instance;
    }//getInstance

    /** login */
    public synchronized Employee login(String username, String password) throws Exception
    {
        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Generate prepared statement and find employee via SQL.
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM employee where username=? AND password=?");

        try
        {
            // Prevent SQL injection
            stmt.setString(1, username);
            stmt.setString(2, password);

            // Execute query
            ResultSet rs = stmt.executeQuery();
//            stmt.close(); // close the connection

            // If we found a record, determine what to do.
            if (rs.next())
            {
                // Define the employee id
                String emp_id = rs.getString("id");
                String emp_position = rs.getString("emp_position");

                //Employee employee = readRecord(rs, conn);

                // See if employee is a salesperson
                if (emp_position.equals(Employee.SALESPERSON))
                {
                    return SalespersonDAO.getInstance().read(emp_id, conn);
                }
            }//if

            // Throw a data exception because we couldn't find the username with the given password
            throw new DataException("Username and/or password incorrect.");
        } finally
        {
            // Ensure we close the connection no matter what.
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }
    }

    ////////////////////////////////////////////
    ///   CREATE methods
    /** Creates a new Employee in the database   */
    public Employee create(String id) throws DataException
    {
        // Create new Employee with Guid
        Employee employee = new Employee(id);
        employee.setObjectAlreadyInDB(false); // set alreadyInDB to false

        // put Employee in cache
        Cache.getInstance().put(employee.getId(), employee);
        return employee;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /** Reads an existing Employee from the database   */
    public Employee read(String id) throws DataException
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (Employee) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try
        {
            return read(id, conn);
        } catch (Exception e)
        {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally
        {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to read an existing Employee from the database    */
    synchronized Employee read(String id, Connection conn) throws Exception
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (Employee) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM employee where id=?");

        try
        {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next())
            {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("Employee with id '" + id + "' not found.");
        } finally
        {
            stmt.close();
        }
    }//read

    /**
     * Internal method to create a Employee object from a record
     * @return employee
     */
    synchronized Employee readRecord(ResultSet rs, Connection conn) throws Exception
    {
        // Check cache for object
        if (Cache.getInstance().containsKey(rs.getString("id")))
        {
            return (Employee) Cache.getInstance().get(rs.getString("id"));
        }

        // Instantiate new employee
        Employee employee = new Employee(rs.getString("id"));

        // Object is already in database
        employee.setObjectAlreadyInDB(true);

        // Set each attribute to object
        employee.setUsername(rs.getString("username"));
        employee.setPassword(rs.getString("password"));
        employee.setPosition(rs.getString("emp_position"));

        // Set Person values
        PersonDAO.getInstance().setPersonValues(employee, conn);

        // Object is not dirty
        employee.setDirty(false);

        // Put object in cache
        Cache.getInstance().put(employee.getId(), employee);

        return employee;
    }//readRecord

    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing employee in the database */
    public void save(Employee employee) throws DataException
    {
        // get a connection from the pool, save all of the sales first using the other employee method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try
        {
            save(employee, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e)
        {
            try
            {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2)
            {
                throw new DataException("Could not roll back the employee database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the employee object information.", e);
        } finally
        {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a employee in the database */
    void save(Employee employee, Connection conn) throws Exception
    {
        // put the employee object to cache
        Cache.getInstance().put(employee.getId(), employee);

        // decide whether to update or insert into the database
        if (employee.isObjectAlreadyInDB() && employee.isDirty())
        {
            // Update Person
            PersonDAO.getInstance().saveNoCommit(employee, conn);

            // Update Employee
            update(employee, conn);
        } else if (employee.isDirty())
        {
            // Insert Person
            PersonDAO.getInstance().saveNoCommit(employee, conn);

            // Insert Employee
            insert(employee, conn);
        }//if
    }

    /** External method to update or create a person in the database without committing connection */
    public void saveNoCommit(Employee employee, Connection conn, Boolean alreadyInDB) throws Exception
    {
        // decide whether to update or insert into the database
        if (alreadyInDB)
        {
            // Insert Person
            PersonDAO.getInstance().saveNoCommit(employee, conn);

            // Update Employee
            update(employee, conn);
        } else
        {
            // Insert Person
            PersonDAO.getInstance().saveNoCommit(employee, conn);

            // Insert Employee
            insert(employee, conn);
        }
    }

    /** Saves an existing employee to the database */
    private void update(Employee employee, Connection conn) throws Exception
    {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE employee SET username=?, password=?, emp_position=?  WHERE id=?");
        try
        {
            // Get Person Attributes and add them to the statement
            stmt.setString(1, employee.getUsername());
            stmt.setString(2, employee.getPassword());
            stmt.setString(3, employee.getPosition());
            stmt.setString(4, employee.getId());
            stmt.execute();

            // Person is clean!
            employee.setDirty(false);
        } finally
        {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new employee into the database */
    private void insert(Employee employee, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO employee (id, username, password, emp_position) VALUES (?, ?, ?, ?)");
        try
        {
            // Set Employee Attributes
            stmt.setString(1, employee.getId());
            stmt.setString(2, employee.getUsername());
            stmt.setString(3, employee.getPassword());
            stmt.setString(4, employee.getPosition());
            stmt.execute();

            employee.setObjectAlreadyInDB(true); // now in DB
            employee.setDirty(false); // object is clean
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(Employee employee) throws DataException
    {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Retrieves all conceptualcars from the database */
    public List<Employee> getAll() throws DataException
    {
        //establish a connection, use a prepared statement, plug it into the search method return a list of empoyees
        return null;
    }

    ////////////////////////////////////////////////
    ///   Set methods
    /** sets all the employee values necessary */
    public Employee setEmpValues(Employee employee, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM employee where id=?");
        try
        {
            // Prevent SQL injection
            stmt.setString(1, employee.getId());

            // Execute query
            ResultSet rs = stmt.executeQuery();

            // If we found a record, determine what to do.
            if (rs.next())
            {
                // Set each attribute to object
                employee.setUsername(rs.getString("username"));
                employee.setPassword(rs.getString("password"));
                employee.setPosition(rs.getString("emp_position"));

                // Set values on Person
                PersonDAO.getInstance().setPersonValues(employee, conn);

                return employee;
            }

            // Throw a data exception because we couldn't find the username with the given password
            throw new DataException("Employee with id '" + employee.getId() + "' not found.");
        } finally
        {
            stmt.close();
        }
    }
}
